package com.qp.basics.common.poi;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel操作类。
 * 本类提供了简单的获取指定cell给指定cell添加内容的方法。
 * <p>
 * 注意遵照java的习惯，row从0开始算，不过excel对单元格标注的习惯是从1开始算，这个需要留意。
 */
public class ExcelX {
	private static final Log log = LogFactory.getLog(ExcelX.class);
	Workbook wb;
	private final static SimpleDateFormat fullTimeFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private final static SimpleDateFormat fullDateFmt = new SimpleDateFormat("yyyy-MM-dd");

	private boolean isHSSF;

	/**
	 * 构造函数内调用，判断是2003的还是2007的
	 */
	private void setHSSF() {
		isHSSF = wb instanceof HSSFWorkbook;
	}

	public RichTextString createRichText(String text) {
		if (isHSSF) {
			return new HSSFRichTextString(text);
		} else {
			return new XSSFRichTextString(text);
		}
	}

	public FormulaEvaluator createFormulaEvaluator() {
		if (isHSSF) {
			return new HSSFFormulaEvaluator((HSSFWorkbook) wb);
		} else {
			return new XSSFFormulaEvaluator((XSSFWorkbook) wb);
		}
	}

	public ClientAnchor createClientAnchor(int colNum, int rowNum) {
		if (isHSSF) {
			return new HSSFClientAnchor(0, 0, 1023, 255, (short) colNum, rowNum, (short) colNum, rowNum);
		} else {
			return new XSSFClientAnchor(0, 0, 1023, 255, (short) colNum, rowNum, (short) colNum, rowNum);
		}
	}

	public ExcelX(InputStream is) {
		try {
			wb = WorkbookFactory.create(is);
			setHSSF();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ExcelX() {
		wb = new XSSFWorkbook();
		setHSSF();
	}

	public ExcelX(String filePath) {

		try {
			wb = WorkbookFactory.create(new File(filePath));
			setHSSF();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	class Point {
		public Point(String cellPositionStr) {
			char[] chars = cellPositionStr.toCharArray();
			int i = 0;
			for (; i < chars.length; i++) {
				if (Character.isDigit(chars[i])) {
					break;
				}
			}
			row = Integer.parseInt(cellPositionStr.substring(i)) - 1;
			col = cellNumStr2Int(cellPositionStr.substring(0, i));
		}

		public Point(String colStr, int row) {
			col = cellNumStr2Int(colStr);
			this.row = row;
		}

		int row;
		int col;
	}

	/**
	 * 获取sheet数目。
	 *
	 * @return
	 */
	public int getSheetCnt() {
		return this.wb.getNumberOfSheets();
	}

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 *
	 * @param cellPositionStr 位置参数如A12表示A列，12行。
	 * @param sheetNo
	 * @param v
	 * @return
	 */
	public Cell setCellValue(String cellPositionStr, int sheetNo, Object v) {
		Point p = new Point(cellPositionStr);
		return setCellValue(p, sheetNo, v);
	}

	public Cell setCellValue(String cellPositionStr, Object v) {
		Point p = new Point(cellPositionStr);
		return setCellValue(p, 0, v);
	}

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 *
	 * @param colNumStr 哪一列
	 * @param rowNum
	 * @param sheetNo
	 * @param v
	 * @return
	 */
	public Cell setCellValue(String colNumStr, int rowNum, int sheetNo, Object v) {
		Point p = new Point(colNumStr, rowNum);
		return setCellValue(p, sheetNo, v);
	}

	public Cell setCellValue(Point p, int sheetNo, Object v) {
		return setCellValue(p.col, p.row, sheetNo, v);
	}

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 *
	 * @param colNum
	 * @param rowNum  从0开始。
	 * @param sheetNo 从0开始。
	 * @param v
	 * @return
	 */
	public Cell setCellValue(int colNum, int rowNum, int sheetNo, Object v) {
		Cell cell = this.getCell(colNum, rowNum, sheetNo);
		if (cell == null) {
			cell = createCell(colNum, rowNum, sheetNo);
		}
		if (v == null) {
			cell.setCellValue(createRichText(""));//TODO 添加的值是以单元格格式为准，还是以数据类型为准？
			return cell;
		}
		if (v.getClass() == Boolean.class) {
			cell.setCellValue((Boolean) v);
		} else if (v.getClass() == Integer.class) {
			cell.setCellValue((Integer) v);
		} else if (v.getClass() == Double.class) {
			cell.setCellValue((Double) v);
		} else if (v.getClass() == Float.class) {
			cell.setCellValue((Float) v);
		} else if (v.getClass() == BigDecimal.class) {
			cell.setCellValue(((BigDecimal) v).doubleValue());
		} else if (v instanceof Date) {
			cell.setCellValue(createRichText(fullTimeFmt.format((Date) v)));//TODO 权益之计
		} else if (v.getClass() == String.class) {
			String cellStr = (String) v;
			if (cellStr.length() >= 32766) {
				cellStr = cellStr.substring(0, 32765);
				if (log.isWarnEnabled()) {
					log.warn("str has been substring(0, 32765) for cell (" + sheetNo + "," + rowNum + "," + colNum + ")");
				}
			}
			cell.setCellValue(createRichText(cellStr));
//		} else if (v.getClass().getName().equals("oracle.sql.TIMESTAMP")) {
//			oracle.sql.TIMESTAMP vx = (oracle.sql.TIMESTAMP) v;
//			try {
//				cell.setCellValue(createRichText(fullTimeFmt.format(vx.timestampValue())));
//			} catch (SQLException e) {
//				throw new RuntimeException(e.getMessage(), e);
//			}
		} else if (v instanceof CellPicture) {
			CellPicture p = (CellPicture) v;
			try {
				//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
				ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
				BufferedImage bufferImg = ImageIO.read(p.getFile());
				ImageIO.write(bufferImg, "jpg", byteArrayOut);

				//读进一个excel模版
				Sheet sheet = wb.getSheetAt(sheetNo);
				;
				Drawing patriarch = sheet.createDrawingPatriarch();
				;
				/*
				 * @param dx1   the x coordinate within the first cell.
				 * @param dy1   the y coordinate within the first cell.
				 * @param dx2   the x coordinate within the second cell.
				 * @param dy2   the y coordinate within the second cell.
				 * @param col1  the column (0 based) of the first cell.
				 * @param row1  the row (0 based) of the first cell.
				 * @param col2  the column (0 based) of the second cell.
				 * @param row2  the row (0 based) of the second cell.
				 * */
				ClientAnchor anchor = createClientAnchor(colNum, rowNum);
				patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG));
			} catch (Exception e) {
				throw new RuntimeException(e.getMessage(), e);
			}

		} else {
			cell.setCellValue(createRichText(v.toString()));
		}
		return cell;
	}

	public Cell createCell(int colNum, int rowNum, int sheetNo) {
		Row row = getRow(rowNum, sheetNo);
		if (row == null) row = createRow(rowNum, sheetNo);
		Cell cell = row.createCell(colNum);
		return cell;
	}

	public Row createRow(int rowNum, int sheetNo) {
		Sheet sheet = null;
		if (sheetNo >= wb.getNumberOfSheets()) {
			sheet = wb.createSheet("sheet-" + sheetNo);
		} else {
			sheet = wb.getSheetAt(sheetNo);
		}
		Row row = sheet.getRow(rowNum);
		if (row == null)
			row = sheet.createRow(rowNum);
		return row;
	}

	/**
	 * 根据指定行列和sheet获取单元。
	 *
	 * @param colNum
	 * @param rowNum
	 * @param sheetNo
	 * @return
	 */
	public Cell getCell(int colNum, int rowNum, int sheetNo) {
		Row row = getRow(rowNum, sheetNo);
		if (row == null) return null;
		Cell cell = row.getCell(colNum);
		//if(cell == null)cell = row.createCell(colNum);
		return cell;
	}

	public Cell getCell(String colNumStr, int rowNum, int sheetNo) {
		int colNum = cellNumStr2Int(colNumStr);
		return getCell(colNum, rowNum, sheetNo);
	}

	public Cell getCell(String cellPositionStr, int sheetNo) {
		Point p = new Point(cellPositionStr);
		return getCell(p.col, p.row, sheetNo);
	}

	public Sheet getSheetAt(int num) {
		return wb.getSheetAt(num);
	}

	/**
	 * 获取sheet的名称列表。
	 *
	 * @return
	 */
	public String[] getSheetNames() {
		int n = wb.getNumberOfSheets();
		String[] sheetNames = new String[n];
		for (int i = 0; i < n; i++) {
			sheetNames[i] = wb.getSheetName(i);
		}
		return sheetNames;
	}

	/**
	 * 合并。
	 *
	 * @param sheetNum
	 * @param firstRow
	 * @param lastRow
	 * @param firstCol
	 * @param lastCol
	 */
	public void addMergedRegion(int sheetNum, int firstRow, int lastRow, int firstCol, int lastCol) {
		Sheet sheet = getSheetAt(sheetNum);
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));//指定合并区域
	}

	/**
	 * 获取某一行。
	 *
	 * @param rowNum
	 * @param sheetNo
	 * @return
	 */
	public Row getRow(int rowNum, int sheetNo) {
		Sheet sheet = null;
		if (sheetNo >= wb.getNumberOfSheets()) {
			return null;
		} else {
			sheet = wb.getSheetAt(sheetNo);
		}
		if (sheet == null) return null;
		Row row = sheet.getRow(rowNum);
		//if(row == null)
		//row = sheet.createRow(rowNum);
		return row;
	}

	/**
	 * 将列的名称转换为数字。
	 *
	 * @param cellNumStr
	 * @return
	 */
	private static int cellNumStr2Int(String cellNumStr) {
		cellNumStr = cellNumStr.toLowerCase();
		int cellNum = 0;
		char[] chars = cellNumStr.toCharArray();
		int j = 0;
		for (int i = chars.length - 1; i >= 0; i--) {
			cellNum += (chars[i] - 'a' + 1) * Math.pow(26, j);
			j++;
		}
		return cellNum - 1;
	}

	public static String cellNumIntToStr(int colNum) {
		String colName = "";
//		for(int i=0;i<colNum/26+1;i++){
//			char c = (char)(colNum%26+'a'-i);
//			colName = c + colName;
//			colNum = colNum/26;
//		}
		//colNum++;
		do {
			char c = (char) (colNum % 26 + 'A');
			colName = c + colName;
			colNum = colNum / 26 - 1;
		} while (colNum >= 0);
		return colName;
	}

	/**
	 * 将excel写入到某个输出流中。
	 *
	 * @param out
	 */
	public void write(OutputStream out) {
		try {
			wb.write(out);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}

	public void save(String filePath) {
		try {
			OutputStream out = new FileOutputStream(new File(filePath));
			write(out);
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			throw new RuntimeException(e.getMessage(), e);
		} catch (IOException e) {
			throw new RuntimeException(e.getMessage(), e);
		}
	}

//	public static int covert(String content){
//		int number=0;
//		String [] HighLetter = {"A","B","C","D","E","F"};
//		Map<String,Integer> map = new HashMap<>();
//		for(int i = 0;i <= 9;i++){
//			map.put(i+"",i);
//		}
//		for(int j= 10;j<HighLetter.length+10;j++){
//			map.put(HighLetter[j-10],j);
//		}
//		String[]str = new String[content.length()];
//		for(int i = 0; i < str.length; i++){
//			str[i] = content.substring(i,i+1);
//		}
//		for(int i = 0; i < str.length; i++){
//			number += map.get(str[i])*Math.pow(16,str.length-1-i);
//		}
//		return number;
//	}

	/**
	 * 获取某个单元格的值，并做一定的类型判断。
	 *
	 * @param cell
	 * @return
	 */
	public Object getCellValue(Cell cell) {
		Object value = null;
		if (cell != null) {
			CellType cellType = cell.getCellTypeEnum();
			CellStyle style = cell.getCellStyle();
			short format = style.getDataFormat();
			switch (cellType) {
				case NUMERIC:
					double numTxt = cell.getNumericCellValue();

					Object aa =  cell.getNumericCellValue();

					if(aa.toString().length()==15){
						BigDecimal bd1 = new BigDecimal(numTxt);
						aa = bd1.toString();
					}


					if (format <= 22 && format >= 14) value = DateUtil.getJavaDate(numTxt);
					else if (format <= 47 && format >= 45) value = DateUtil.getJavaDate(numTxt);
					else if (format == 0) value = aa.toString().split("\\.")[0];
						//这个地方的format搞不清楚啥情况，50之内应该是内置的格式
						//但有时也会出现非内置的格式，目前还不清楚啥情况
//				for (int i = 0; i < 255; i++) {
//					short x = (short)i;
//					String xx = DataFormat.getBuiltinFormat(x);
//					System.out.println(i+"==="+xx);
//				}
					else value = aa;
					break;
				case BOOLEAN:
					boolean booleanTxt = cell.getBooleanCellValue();
					value = booleanTxt;
					break;
				case BLANK:
					value = null;
					break;
				case FORMULA:
					FormulaEvaluator eval = createFormulaEvaluator();
					eval.evaluateInCell(cell);
					value = getCellValue(cell);
					break;
				case STRING:
					RichTextString rtxt = cell.getRichStringCellValue();
					if (rtxt == null) {
						break;
					}
					String txt = rtxt.getString();
					value = txt;
					break;
				default:
					//System.out.println(cell.getColumnIndex()+" col cellType="+cellType);
			}
		}
		return value;

	}

	public static interface CellCallback {
		public void handler(Cell cell);
	}

	/**
	 * 遍历所有的单元格。
	 *
	 * @param callback
	 * @param sheetNo
	 */
	public void iterator(CellCallback callback, int sheetNo) {
		Sheet sheet = wb.getSheetAt(sheetNo);
		if (sheet == null) return;
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();
		for (int i = firstRowNum; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			if (row == null) continue;
			for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
				Cell cell = row.getCell(j);
				callback.handler(cell);
			}
		}
	}

	/**
	 * 读取某个excel，然后将其转化为List的List。
	 *
	 * @param sheetNo
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<List> excelToListList(int sheetNo) {
		//首先是讲excel的数据读入，然后根据导入到的数据库的结构和excel的结构来决定如何处理。

		Sheet sheet = wb.getSheetAt(sheetNo);
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();
		List rows = new ArrayList();
		for (int i = firstRowNum; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				rows.add(null);
				continue;
			}
			List cellList = new ArrayList();
			for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
				Object value = null;
				Cell cell = row.getCell(j);
				value = getCellValue(cell);

				cellList.add(value);

			}
			rows.add(cellList);
		}
		return rows;
	}

	/**
	 * 把excel转换成List<Map>格式
	 *
	 * @param sheetNo 需要转换的数据所在的sheet次序号(0,1,2...n)
	 * @return
	 */
	public List<Map> excelToMapList(int sheetNo) {
		Sheet sheet = this.wb.getSheetAt(sheetNo);
		int firstRowNum = sheet.getFirstRowNum();
		return excelToMapList(sheetNo, firstRowNum, firstRowNum + 1);
	}

	/**
	 * 把excel转换成List<Map>格式
	 *
	 * @param sheetNo        需要转换的数据所在的sheet次序号(0,1,2...n)
	 * @param keyRowNo       作为key的行号 （0,1,2...n）
	 * @param dataStartRowNo 第一行数据的行号 （1,2...n）
	 * @return
	 */
	public List<Map> excelToMapList(int sheetNo, int keyRowNo, int dataStartRowNo) {
		return excelToMapList(sheetNo, keyRowNo, keyRowNo, dataStartRowNo);
	}

	/**
	 * 标题从多行进行合并得到。
	 *
	 * @param sheetNo
	 * @param keyRowNoFrom
	 * @param keyRowNoTo
	 * @param dataStartRowNo
	 * @return
	 */
	public List<Map> excelToMapList(int sheetNo, int keyRowNoFrom, int keyRowNoTo, int dataStartRowNo) {
		Sheet sheet = this.wb.getSheetAt(sheetNo);
		List rowMapList = new ArrayList();
		Object[] keyList = new String[200];
		int firstCellNum = 0;//表头开始列
		int lastCellNum = 0;//表头结束列
		for (int i = keyRowNoFrom; i <= keyRowNoTo; i++) {
			Row mapKeyRow = sheet.getRow(i);
			Object lstKey = null;
			if (keyRowNoTo == i) {
				firstCellNum = mapKeyRow.getFirstCellNum();
				lastCellNum = mapKeyRow.getLastCellNum();
			}
			for (int j = mapKeyRow.getFirstCellNum(); j < mapKeyRow.getLastCellNum(); j++) {
				Cell col = mapKeyRow.getCell(j);
				Object key = getCellValue(col);
				if (j >= 200) break;
//	    		Object keyx  = keyList[j];
//	    		if(key == null){
//	    			key = keyx;
//	    		}else if(keyx != null && key instanceof String)key = keyx+(String)key;
//
//	    		if(key == null || "".equals(key)){
//	    			key = lstKey;
//	    		}
				//有时会不小心给表头加上空格，在excel里还不容易看出来。
				if (key != null && key instanceof String) key = ((String) key).trim();
				lstKey = key;
				keyList[j] = key;
			}
		}
		int lastRowNum = sheet.getLastRowNum();
		for (int i = dataStartRowNo; i <= lastRowNum; ++i) {
			Row dataRow = sheet.getRow(i);
			if (dataRow == null) {
				rowMapList.add(null);
				continue;
			}
			Map rowMap = new HashMap();
			for (int j = firstCellNum; j < lastCellNum; ++j) {
				if (j >= 200) break;
				Object key = keyList[j];
				if (key == null || key.equals("")) {
					continue;
				}
				Object value = getCellValue(dataRow.getCell(j));
				rowMap.put(key, value);
			}
			rowMapList.add(rowMap);
		}
		return rowMapList;
	}

	static interface RowCallBack {
		void handler(Map m);
	}

	public void iterateRows(Sheet sheet, RowCallBack callBack, int keyRowNoFrom, int keyRowNoTo, int dataStartRowNo) {
		List rowMapList = new ArrayList();
		String[] keyList = new String[200];
		for (int i = keyRowNoFrom; i <= keyRowNoTo; i++) {
			Row mapKeyRow = sheet.getRow(i);
			String lstKey = null;
			for (int j = mapKeyRow.getFirstCellNum(); j < mapKeyRow.getLastCellNum(); j++) {
				Cell col = mapKeyRow.getCell(j);
				String key = col.getRichStringCellValue().getString();
				String keyx = keyList[j];
				if (key == null) {
					key = keyx;
				} else if (keyx != null) key = keyx + key;

				if (key == null || "".equals(key)) {
					key = lstKey;
				}
				lstKey = key;

				keyList[j] = key;
			}
		}
		int lastRowNum = sheet.getLastRowNum();
		for (int i = dataStartRowNo; i <= lastRowNum; ++i) {
			Row dataRow = sheet.getRow(i);
			if (dataRow == null) continue;
			Map rowMap = new HashMap();
			for (int j = dataRow.getFirstCellNum(); j < dataRow.getLastCellNum(); ++j) {
				String key = keyList[j];
				if (key == null || key.equals("")) {
					continue;
				}
				Object value = getCellValue(dataRow.getCell(j));
				rowMap.put(key, value);
			}
			callBack.handler(rowMap);
		}
	}

	public void mapListToExcel(ExcelX excel, List<Map> rs, Iterator it) {

	}

	/**
	 * 复制srcRowNum，然后在targetRowNum处添加一行。
	 *
	 * @param srcRowNum
	 * @return
	 */
	public Row closeRow(int srcRowNum) {
		Sheet sheet = wb.getSheetAt(0);
		int targetRowNum = sheet.getLastRowNum() + 1;
		return cloneRow(sheet, sheet, srcRowNum, targetRowNum);
	}

	/**
	 * 复制srcRowNum，然后在targetRowNum处添加一行。
	 *
	 * @param srcRowNum
	 * @param targetRowNum
	 * @return
	 */
	public Row cloneRow(int srcRowNum, int targetRowNum) {
		Sheet sheet = wb.getSheetAt(0);
		return cloneRow(sheet, sheet, srcRowNum, targetRowNum);
	}

	/**
	 * 复制srcRowNum，然后在targetRowNum处添加一行。
	 *
	 * @param srcSheet
	 * @param targetSheet
	 * @param srcRowNum
	 * @param targetRowNum
	 * @return
	 */
	public Row cloneRow(Sheet srcSheet, Sheet targetSheet, int srcRowNum, int targetRowNum) {
		Row srcRow = srcSheet.getRow(srcRowNum);
		Row newRow = targetSheet.createRow(targetRowNum);
		newRow.setHeight(srcRow.getHeight());
		int i = 0;
		for (Iterator<Cell> cit = srcRow.cellIterator(); cit.hasNext(); ) {
			Cell Cell = cit.next();
			//Cell中的一些属性转移到Cell中
			org.apache.poi.ss.usermodel.Cell cell = newRow.createCell(i++);
			CellStyle s = Cell.getCellStyle();
			cell.setCellStyle(Cell.getCellStyle());
		}
		return newRow;
	}

	public void deleteRow(int rowNum) {
		deleteRow(0, rowNum);
	}

	public void deleteRow(int sheetNo, int rowNum) {
		Sheet sheet = wb.getSheetAt(sheetNo);
		sheet.shiftRows(rowNum, sheet.getLastRowNum(), -1);
	}

	/**
	 * 拷贝行粘帖到指定位置。
	 *
	 * @param sheet
	 * @param srcRow
	 * @param targetRowNum
	 * @return
	 */
	public Row copyAndInsertRow(Sheet sheet, Row srcRow, int targetRowNum) {
		sheet.shiftRows(targetRowNum, sheet.getLastRowNum(), 1);
		Row newRow = sheet.getRow(targetRowNum);
		newRow.setHeight(srcRow.getHeight());
		int j = 0;
		for (Iterator<Cell> cit = srcRow.cellIterator(); cit.hasNext(); ) {
			Cell Cell = cit.next();
			//Cell中的一些属性转移到Cell中
			org.apache.poi.ss.usermodel.Cell cell = newRow.createCell(Cell.getColumnIndex());
			cell.setCellStyle(Cell.getCellStyle());
		}
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress region = sheet.getMergedRegion(i);
			if (region.getFirstRow() == srcRow.getRowNum()
					&& region.getLastRow() == region.getFirstRow()
			) {
				sheet.addMergedRegion(new CellRangeAddress(targetRowNum, region.getFirstColumn(),
						targetRowNum, region.getLastColumn()));
			}
		}
		return newRow;
	}

	public Row copyAndInsertRow(int sheetNo, int fromRowNum, int targetRowNum) {
		Sheet sheet = wb.getSheetAt(sheetNo);
		Row srcRow = sheet.getRow(fromRowNum);
		return copyAndInsertRow(sheet, srcRow, targetRowNum);
	}

	public Row copyAndInsertRow(int fromRowNum, int targetRowNum) {
		return copyAndInsertRow(0, fromRowNum, targetRowNum);
	}

	public Workbook getWb() {
		return wb;
	}

	public void setWb(Workbook wb) {
		this.wb = wb;
	}

	public void setForceFormulaRecalculation(boolean v) {
		setForceFormulaRecalculation(wb.getSheetAt(0), v);
	}

	public void setForceFormulaRecalculation(Sheet sheet, boolean v) {
		sheet.setForceFormulaRecalculation(v);
	}

	public static void main(String[] args) throws ParseException, FileNotFoundException, IOException {

		//if(true)return;
		ExcelX a=new ExcelX("F:\\attachment\\localhost\\reportTemplatePath\\1.xls");
		Cell c=a.getCell(3,40,0);
		System.out.println(c.getCellType());
		System.out.println(c.getCellFormula());
		System.out.println(c.getNumericCellValue());
//		Cell c=a.createCell(0,46,0);
//		c.setCellValue("");
//		c.setCellType(Cell.CELL_TYPE_BLANK);
//		c.setCellType(Cell.CELL_TYPE_FORMULA);
//		c.setCellFormula("A45+A46");
//		a.save("F:\\attachment\\localhost\\reportTemplatePath\\11.xls");

	}

	public static class CellPicture {
		File f;

		public CellPicture(File f) {
			this.f = f;
		}

		public File getFile() {
			return f;
		}
	}

	//zsk add

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 *
	 * @param colNum
	 * @param rowNum       从0开始。
	 * @param sheetNo      从0开始。
	 * @param customerCell
	 * @return
	 */
	public Cell setCellValue(int colNum, int rowNum, int sheetNo, CustomerCell customerCell) {
		Cell cell = this.getCell(colNum, rowNum, sheetNo);
		if (cell == null) {
			cell = createCell(colNum, rowNum, sheetNo);
		}

		cell = setCellValue(colNum, rowNum, sheetNo, customerCell.getValue());

		if (customerCell.getFontHeight() > 0) {
			CellStyle style = createCellStyle(wb, customerCell.getFontHeight());
			cell.setCellStyle(style);
		}

		if (customerCell.getRowspan() > 1 && customerCell.getColspan() > 1) {
			addMergedRegion(sheetNo, rowNum, rowNum + customerCell.getRowspan() - 1, colNum, colNum + customerCell.getColspan() - 1);
		} else if (customerCell.getRowspan() > 1) {
			addMergedRegion(sheetNo, rowNum, rowNum + customerCell.getRowspan() - 1, colNum, colNum);
		} else if (customerCell.getColspan() > 1) {
			addMergedRegion(sheetNo, rowNum, rowNum, colNum, colNum + customerCell.getColspan() - 1);
		}
		return cell;
	}


	private static Map<String, CellStyle> styleMap = new HashMap();

	//使用map缓存，避免重复创建
	private static CellStyle createCellStyle(Workbook workbook, short fontsize) {
		String key = "" + fontsize; //暂时key只根据fontsize生成
		if (styleMap.containsKey(key)) {
			return styleMap.get(key);
		}

		CellStyle style = workbook.createCellStyle();
//		style.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
//		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
		//创建字体
		Font font = workbook.createFont();
//		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints(fontsize);
		//加载字体
		style.setFont(font);

		styleMap.put(key, style);
		return style;
	}
}

